package easybooking.db;

import easybooking.data.Type;
import easybooking.data.TypeImpl;
import java.rmi.RemoteException;
import java.util.Vector;
import java.sql.*;

/**
 * Mapper-Class, which displays <code>Type</code> objects in a relational
 * database. Therefore a collection of methods to search, create and modify
 * will be provided.
 * The mapping is bidirectional, which means, that objects could be transformed
 * into database-structures and database-structures into objects.
 *
 * @author Mark Roman Stehle
 */
// <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
// #[regen=yes,id=DCE.19B2243D-9856-1927-CD4C-4235CB798EA0]
// </editor-fold> 
public class TypeMapper extends Mapper {

    /**
     * The class <code>TypeMapper</code> will be instantiated only once.
     * This is called <b>Singleton</b>, because of the <code>static</code>-tag,
     * the variable exists only once for all instances of this class. The
     * variable saves the only instance of this class.
     *
     * @see typeMapper()
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
    // #[regen=yes,id=DCE.150FEFE6-BD96-AF8B-9B10-6604E2A53D44]
    // </editor-fold> 
    private static TypeMapper typeMapper = null;

    /**
     * The protected constructor prevents the possibility to generate a new
     * instance of this class.
     */
    protected TypeMapper() {
    }

    /**
     * The static method can be called with
     * <code>TypeMapper.typeMapper()</code>.
     * This guarantees the singleton property by checking so that only one
     * instance of the <code>TypeMapper</code> exists.

     * <b>Conclusion:</b> The <code>TypeMapper</code> should not be
     * instantiated with <code>new</code>, but rather by calling the static
     * method.

     * @return The <code>TypeMapper</code> object.
     * @see typeMapper
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
    // #[regen=yes,id=DCE.708ED186-0A18-8142-650A-E02077118C8A]
    // </editor-fold> 
    public static TypeMapper typeMapper() {
        if (typeMapper == null) {
            typeMapper = new TypeMapper();
        }
        return typeMapper;
    }

    /**
     * Searching a type with given key, since the key is distinct only one
     * object will be returned.
     *
     * @param id primary key (->DB)
     * @return Type object, which corresponds the given key,
     * <code>null</code> for a non existing database tupel.
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
    // #[regen=yes,id=DCE.EA8B61AE-396C-2804-C5F8-537F9DF5422B]
    // </editor-fold> 
    public Type findByKey(int id) {
        Connection con = DBConnection.connection();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(
                    "SELECT typeId, name, rows, columns, isActive FROM Type " +
                    "WHERE typeId=" + id);
            if (rs.next()) {
                try {
                    Type t = new TypeImpl();
                    t.setTypeId(rs.getInt("typeId"));
                    t.setName(rs.getString("name"));
                    t.setRows(rs.getInt("rows"));
                    t.setColumns(rs.getInt("columns"));
                    t.setIsActive(rs.getBoolean("isActive"));
                    return t;
                } catch (Exception ex) {
                    ex.printStackTrace();
                    return null;
                }
            }
        } catch (SQLException e2) {
            e2.printStackTrace();
            return null;
        }
        return null;
    }

    /**
     * Selecting all active types from the database.
     *
     * @return A vector of type objects, which includes all active types.
     * In case of an exception, an empty vector will be returned.
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
    // #[regen=yes,id=DCE.3A7E88AD-076F-EB18-CCB6-278DA19F552A]
    // </editor-fold> 
    public Vector<Type> findAll() {
        Connection con = DBConnection.connection();
        Vector<Type> result = new Vector<Type>();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(
                    "SELECT typeId, name, rows, columns, isActive FROM Type " +
                    "WHERE isActive = true " +
                    "ORDER BY typeId ASC");
            while (rs.next()) {
                try {
                    Type t = new TypeImpl();
                    t.setTypeId(rs.getInt("typeId"));
                    t.setName(rs.getString("name"));
                    t.setRows(rs.getInt("rows"));
                    t.setColumns(rs.getInt("columns"));
                    result.addElement(t);
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        } catch (SQLException e2) {
            e2.printStackTrace();
        }
        return result;
    }

    /**
     * Inserting a <code>Type</code> object into the database. Before that,
     * the primary key of the given object will be verified und corrected.
     *
     * @param t the object to be saved
     * @return The given object, with corrected primary key.
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
    // #[regen=yes,id=DCE.5B728BA7-2B1D-A0A8-279E-833ECF76F190]
    // </editor-fold> 
    public Type insert(Type t) {
        Connection con = DBConnection.connection();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(
                    "SELECT MAX(typeId) AS maxTypeId " +
                    "FROM Type");
            if (rs.next()) {
                try {
                    t.setTypeId(rs.getInt("maxTypeId") + 1);
                    stmt = con.createStatement();
                    stmt.executeUpdate("INSERT INTO Type (typeId, name, rows, columns, isActive) " +
                            "VALUES (" +
                            t.getTypeId() + ",'" +
                            t.getName() + "','" +
                            t.getRows() + "','" +
                            t.getColumns() + "','" +
                            booleanToInt(t.getIsActive()) + "')");
                } catch (RemoteException ex) {
                    ex.printStackTrace();
                }
            }
        } catch (SQLException e2) {
            e2.printStackTrace();
        }
        return t;
    }

    /**
     * Rewriting an object into the database.
     *
     * @param t the object which sould be written into the database
     * @return The given object.
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
    // #[regen=yes,id=DCE.D1677323-EEE2-DC5E-8422-02D19D2000B0]
    // </editor-fold> 
    public Type update(Type t) {
        Connection con = DBConnection.connection();
        try {
            Statement stmt = con.createStatement();
            stmt.executeUpdate("UPDATE Type " +
                    "SET name=\"" + t.getName() + "\", " +
                    "rows=\"" + t.getRows() + "\", " +
                    "columns=\"" + t.getColumns() + "\", " +
                    "isActive=\"" + booleanToInt(t.getIsActive()) + "\" " +
                    "WHERE typeId=" + t.getTypeId());
        } catch (SQLException e2) {
            e2.printStackTrace();
        } catch (RemoteException e) {
            e.printStackTrace();
        }
        return t;
    }

    /**
     * Selecting all active types from the database with a certain name,
     * which is given through a parameter.
     *
     * @param name the name or part of a name which should be searched for
     * @return A vector of type objects, which includes all active types
     * with a certain name.
     * In case of an exception, an empty vector will be returned.
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
    // #[regen=yes,id=DCE.9BA03485-4EC3-DA8D-A572-5679A4CD6A05]
    // </editor-fold> 
    public Vector<Type> findByName(String name) {
        Connection con = DBConnection.connection();
        Vector<Type> result = new Vector<Type>();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(
                    "SELECT typeId, name, rows, columns, isActive FROM Type " +
                    "WHERE name LIKE '%" + name + "%' " +
                    "AND isActive = true " +
                    "ORDER BY typeId ASC");
            while (rs.next()) {
                try {
                    Type t = new TypeImpl();
                    t.setTypeId(rs.getInt("typeId"));
                    t.setName(rs.getString("name"));
                    t.setRows(rs.getInt("rows"));
                    t.setColumns(rs.getInt("columns"));
                    t.setIsActive(rs.getBoolean("isActive"));
                    result.addElement(t);
                } catch (Exception ex) {
                    ex.printStackTrace();
                    break;
                }
            }
        } catch (SQLException e2) {
            e2.printStackTrace();
        }
        return result;
    }

    /**
     * Selecting all active type from the database by minimum seats total,
     * which is given through a parameter.
     *
     * @param minSeats the minimal number of total seats which should be
     * searched for
     * @return A vector of type objects, which includes all active types
     * with minium seats total
     * In case of an exception, an empty vector will be returned.
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
    // #[regen=yes,id=DCE.79D7D995-177D-0A26-D321-71EEEC18BEB9]
    // </editor-fold> 
    public Vector<Type> findByMinSeatsTotal(int minSeats) {
        Connection con = DBConnection.connection();
        Vector<Type> result = new Vector<Type>();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(
                    "SELECT typeId, name, rows, columns, isActive, (rows * columns) AS maxSeats FROM Type " +
                    "WHERE " + minSeats + " >= maxSeats " +
                    "AND isActive = true " +
                    "ORDER BY typeId ASC");
            while (rs.next()) {
                try {
                    Type t = new TypeImpl();
                    t.setTypeId(rs.getInt("typeId"));
                    t.setName(rs.getString("name"));
                    t.setRows(rs.getInt("rows"));
                    t.setColumns(rs.getInt("columns"));
                    t.setIsActive(rs.getBoolean("isActive"));
                    result.addElement(t);
                } catch (Exception ex) {
                    ex.printStackTrace();
                    break;
                }
            }
        } catch (SQLException e2) {
            e2.printStackTrace();
        }
        return result;
    }
}

